--    Program: 		CAPARIMP_RECEIPT.sql
--    Description:  Import AR RECEIPT information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         2-Feb-2006

set doc off      
set verify off
set serveroutput on size 1000000
--whenever sqlerror exit failure rollback
ALTER TRIGGER AR_CASH_RECEIPTS_CT1 DISABLE;
ALTER TRIGGER AX_AR_CASH_RECEIPTS_ARD1 DISABLE;
ALTER TRIGGER AX_AR_CASH_RECEIPTS_ARI1 DISABLE;
ALTER TRIGGER AX_AR_CASH_RECEIPTS_ARU1 DISABLE;
ALTER TRIGGER AX_AR_CASH_RECEIPTS_ARU2 DISABLE;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_cash_receipt_id		number;
                    		                 		
   cursor cur_ar_receipt is
      select * from CRST_AR_CASH_RECEIPTS_ALL
      --WHERE CASH_RECEIPT_ID = 16694
	  FOR UPDATE;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_receipt loop   	
     l_err_msg_tmp := '';
     
     SELECT AR_CASH_RECEIPTS_S.NEXTVAL 
     INTO l_cash_receipt_id 
     FROM DUAL;
     
     update CRST_AR_CASH_RECEIPTS_ALL
     set    cash_receipt_id_n = l_cash_receipt_id 
     where  cash_receipt_id = cur.cash_receipt_id;
     
     begin
       -- import data
       insert into AR_CASH_RECEIPTS_ALL
       (
          CASH_RECEIPT_ID                 ,
          LAST_UPDATED_BY                 ,
          LAST_UPDATE_DATE                ,
          LAST_UPDATE_LOGIN               ,
          CREATED_BY                      ,
          CREATION_DATE                   ,
          AMOUNT                          ,
          SET_OF_BOOKS_ID                 ,
          CURRENCY_CODE                   ,
          RECEIVABLES_TRX_ID              ,
          PAY_FROM_CUSTOMER               ,
          STATUS                          ,
          TYPE                            ,
          RECEIPT_NUMBER                  ,
          RECEIPT_DATE                    ,
          MISC_PAYMENT_SOURCE             ,
          COMMENTS                        ,
          DISTRIBUTION_SET_ID             ,
          REVERSAL_DATE                   ,
          REVERSAL_CATEGORY               ,
          REVERSAL_REASON_CODE            ,
          REVERSAL_COMMENTS               ,
          EXCHANGE_RATE_TYPE              ,
          EXCHANGE_RATE                   ,
          EXCHANGE_DATE                   ,
          ATTRIBUTE_CATEGORY              ,
          ATTRIBUTE1                      ,
          ATTRIBUTE2                      ,
          ATTRIBUTE3                      ,
          ATTRIBUTE4                      ,
          ATTRIBUTE5                      ,
          ATTRIBUTE6                      ,
          ATTRIBUTE7                      ,
          ATTRIBUTE8                      ,
          ATTRIBUTE9                      ,
          ATTRIBUTE10                     ,
          REMITTANCE_BANK_ACCOUNT_ID      ,
          ATTRIBUTE11                     ,
          ATTRIBUTE12                     ,
          ATTRIBUTE13                     ,
          ATTRIBUTE14                     ,
          ATTRIBUTE15                     ,
          CONFIRMED_FLAG                  ,
          CUSTOMER_BANK_ACCOUNT_ID        ,
          CUSTOMER_SITE_USE_ID            ,
          DEPOSIT_DATE                    ,
          PROGRAM_APPLICATION_ID          ,
          PROGRAM_ID                      ,
          PROGRAM_UPDATE_DATE             ,
          RECEIPT_METHOD_ID               ,
          REQUEST_ID                      ,
          SELECTED_FOR_FACTORING_FLAG     ,
          SELECTED_REMITTANCE_BATCH_ID    ,
          FACTOR_DISCOUNT_AMOUNT          ,
          USSGL_TRANSACTION_CODE          ,
          USSGL_TRANSACTION_CODE_CONTEXT  ,
          DOC_SEQUENCE_VALUE              ,
          DOC_SEQUENCE_ID                 ,
          VAT_TAX_ID                      ,
          REFERENCE_TYPE                  ,
          REFERENCE_ID                    ,
          CUSTOMER_RECEIPT_REFERENCE      ,
          OVERRIDE_REMIT_ACCOUNT_FLAG     ,
          ORG_ID                          ,
          ANTICIPATED_CLEARING_DATE       ,
          GLOBAL_ATTRIBUTE1               ,
          GLOBAL_ATTRIBUTE2               ,
          GLOBAL_ATTRIBUTE3               ,
          GLOBAL_ATTRIBUTE4               ,
          GLOBAL_ATTRIBUTE5               ,
          GLOBAL_ATTRIBUTE6               ,
          GLOBAL_ATTRIBUTE7               ,
          GLOBAL_ATTRIBUTE8               ,
          GLOBAL_ATTRIBUTE9               ,
          GLOBAL_ATTRIBUTE10              ,
          GLOBAL_ATTRIBUTE11              ,
          GLOBAL_ATTRIBUTE12              ,
          GLOBAL_ATTRIBUTE13              ,
          GLOBAL_ATTRIBUTE14              ,
          GLOBAL_ATTRIBUTE15              ,
          GLOBAL_ATTRIBUTE16              ,
          GLOBAL_ATTRIBUTE17              ,
          GLOBAL_ATTRIBUTE18              ,
          GLOBAL_ATTRIBUTE19              ,
          GLOBAL_ATTRIBUTE20              ,
          GLOBAL_ATTRIBUTE_CATEGORY       ,
          ISSUER_NAME                     ,
          ISSUE_DATE                      ,
          ISSUER_BANK_BRANCH_ID           ,
          CUSTOMER_BANK_BRANCH_ID         ,
          MRC_EXCHANGE_RATE_TYPE          ,
          MRC_EXCHANGE_RATE               ,
          MRC_EXCHANGE_DATE               ,
          PAYMENT_SERVER_ORDER_NUM        ,
          APPROVAL_CODE                   ,
          ADDRESS_VERIFICATION_CODE       ,
          TAX_RATE                        ,
          ACTUAL_VALUE_DATE               ,
          POSTMARK_DATE                   ,
          APPLICATION_NOTES                
       )
       values
       (
          l_cash_receipt_id                   ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
          cur.LAST_UPDATE_DATE                ,
          cur.LAST_UPDATE_LOGIN               ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
          cur.CREATION_DATE                   ,
          cur.AMOUNT                          ,
          CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
          cur.CURRENCY_CODE                   ,
          CAPARIMP_MAPPING.GetReceivableActivityIDMapping(cur.ORG_ID, cur.RECEIVABLES_TRX_ID),
          CAPARIMP_MAPPING.GetHZCustAccountIDMapping(cur.PAY_FROM_CUSTOMER),
          cur.STATUS                          ,
          cur.TYPE                            ,
          cur.RECEIPT_NUMBER                  ,
          cur.RECEIPT_DATE                    ,
          cur.MISC_PAYMENT_SOURCE             ,
          cur.COMMENTS                        ,
          CAPARIMP_MAPPING.GetDistributionSetIDMapping(cur.DISTRIBUTION_SET_ID),
          cur.REVERSAL_DATE                   ,
          cur.REVERSAL_CATEGORY               ,
          cur.REVERSAL_REASON_CODE            ,
          cur.REVERSAL_COMMENTS               ,
          cur.EXCHANGE_RATE_TYPE              ,
          cur.EXCHANGE_RATE                   ,
          cur.EXCHANGE_DATE                   ,
          cur.ATTRIBUTE_CATEGORY              ,
          cur.ATTRIBUTE1                      ,
          cur.ATTRIBUTE2                      ,
          cur.ATTRIBUTE3                      ,
          cur.ATTRIBUTE4                      ,
          cur.ATTRIBUTE5                      ,
          cur.ATTRIBUTE6                      ,
          cur.ATTRIBUTE7                      ,
          cur.ATTRIBUTE8                      ,
          cur.ATTRIBUTE9                      ,
          cur.ATTRIBUTE10                     ,
          CAPBRIMP_MAPPING.GetBankACMapping(cur.REMITTANCE_BANK_ACCOUNT_ID),
          cur.ATTRIBUTE11                     ,
          cur.ATTRIBUTE12                     ,
          cur.ATTRIBUTE13                     ,
          cur.ATTRIBUTE14                     ,
          cur.ATTRIBUTE15                     ,
          cur.CONFIRMED_FLAG                  ,
          CAPBRIMP_MAPPING.GetBankACMapping(cur.CUSTOMER_BANK_ACCOUNT_ID),
          CAPARIMP_MAPPING.GetHZSiteUseIDMapping(cur.CUSTOMER_SITE_USE_ID),
          cur.DEPOSIT_DATE                    ,
          cur.PROGRAM_APPLICATION_ID          ,
          cur.PROGRAM_ID                      ,
          cur.PROGRAM_UPDATE_DATE             ,
          cur.RECEIPT_METHOD_ID               ,
          cur.REQUEST_ID                      ,
          cur.SELECTED_FOR_FACTORING_FLAG     ,
          CAPARIMP_MAPPING.GetReceiptBatchIDMapping(cur.SELECTED_REMITTANCE_BATCH_ID),
          cur.FACTOR_DISCOUNT_AMOUNT          ,
          cur.USSGL_TRANSACTION_CODE          ,
          cur.USSGL_TRANSACTION_CODE_CONTEXT  ,
          cur.DOC_SEQUENCE_VALUE              ,
          cur.DOC_SEQUENCE_ID                 ,
          cur.VAT_TAX_ID                      ,
          cur.REFERENCE_TYPE                  ,
          cur.REFERENCE_ID                    ,
          cur.CUSTOMER_RECEIPT_REFERENCE      ,
          cur.OVERRIDE_REMIT_ACCOUNT_FLAG     ,
          CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
          cur.ANTICIPATED_CLEARING_DATE       ,
          cur.GLOBAL_ATTRIBUTE1               ,
          cur.GLOBAL_ATTRIBUTE2               ,
          cur.GLOBAL_ATTRIBUTE3               ,
          cur.GLOBAL_ATTRIBUTE4               ,
          cur.GLOBAL_ATTRIBUTE5               ,
          cur.GLOBAL_ATTRIBUTE6               ,
          cur.GLOBAL_ATTRIBUTE7               ,
          cur.GLOBAL_ATTRIBUTE8               ,
          cur.GLOBAL_ATTRIBUTE9               ,
          cur.GLOBAL_ATTRIBUTE10              ,
          cur.GLOBAL_ATTRIBUTE11              ,
          cur.GLOBAL_ATTRIBUTE12              ,
          cur.GLOBAL_ATTRIBUTE13              ,
          cur.GLOBAL_ATTRIBUTE14              ,
          cur.GLOBAL_ATTRIBUTE15              ,
          cur.GLOBAL_ATTRIBUTE16              ,
          cur.GLOBAL_ATTRIBUTE17              ,
          cur.GLOBAL_ATTRIBUTE18              ,
          cur.GLOBAL_ATTRIBUTE19              ,
          cur.GLOBAL_ATTRIBUTE20              ,
          cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
          cur.ISSUER_NAME                     ,
          cur.ISSUE_DATE                      ,
          cur.ISSUER_BANK_BRANCH_ID           ,
          cur.CUSTOMER_BANK_BRANCH_ID         ,
          cur.MRC_EXCHANGE_RATE_TYPE          ,
          cur.MRC_EXCHANGE_RATE               ,
          cur.MRC_EXCHANGE_DATE               ,
          cur.PAYMENT_SERVER_ORDER_NUM        ,
          cur.APPROVAL_CODE                   ,
          cur.ADDRESS_VERIFICATION_CODE       ,
          cur.TAX_RATE                        ,
          cur.ACTUAL_VALUE_DATE               ,
          cur.POSTMARK_DATE                   ,
          cur.APPLICATION_NOTES                                      
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_RECEIPT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/

ALTER TRIGGER AR_CASH_RECEIPTS_CT1 ENABLE;
ALTER TRIGGER AX_AR_CASH_RECEIPTS_ARD1 ENABLE;
ALTER TRIGGER AX_AR_CASH_RECEIPTS_ARI1 ENABLE;
ALTER TRIGGER AX_AR_CASH_RECEIPTS_ARU1 ENABLE;
ALTER TRIGGER AX_AR_CASH_RECEIPTS_ARU2 ENABLE;
--exit
